﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Util;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
    public class Tracks
    {
       
        public bool SendTrack(TrackInfo tr)
        {

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;

                String strSQL = "INSERT INTO Tracks (Name, Price, Path, AuthorID) VALUES (@name, @price, @filepath, @author)";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@author";
                param.Value = tr.Author;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@name";
                param.Value = tr.Name;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@price";
                param.Value = tr.Price;
                param.SqlDbType = SqlDbType.Float;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@filepath";
                param.Value = tr.FilePath;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                con.Open();

                try
                {
                    comm.ExecuteNonQuery();
                }
                catch (System.Data.SqlClient.SqlException)
                {
                    return false;
                }
            }

            return true;
        }

        public List<TrackInfo> GetTracks()
        {
            SqlDataReader sdr;
            List<TrackInfo> trList = new List<TrackInfo>();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                String strSQL = "SELECT Name, Price, TrackID, AuthorID, Path FROM Tracks  WHERE Visible=1";
                SqlCommand comm = new SqlCommand(strSQL, con);

                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        trList.Add(new TrackInfo() { Name = sdr["Name"].ToString(), TrackID = sdr["TrackID"].ToString(), Price = sdr["Price"].ToString(), Author = sdr["AuthorID"].ToString(), FilePath = sdr["Path"].ToString() });
                    }
                }
            }
            return trList;
        }

        public List<TrackInfo> GetTracksOfAuthor(string author)
        {
            SqlDataReader sdr;
            List<TrackInfo> trList = new List<TrackInfo>();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                String strSQL = "SELECT Name, Price, AuthorID,TrackID, Path FROM Tracks WHERE AuthorID = @author";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@author";
                param.Value = author;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        trList.Add(new TrackInfo() { Name = sdr["Name"].ToString(), TrackID = sdr["TrackID"].ToString(), Price = sdr["Price"].ToString(), Author = sdr["AuthorID"].ToString(), FilePath = sdr["Path"].ToString() });
                    }
                }
            }
            return trList;
        }

        public TrackInfo Track(string trackID)
        {
            SqlDataReader sdr;
            TrackInfo track=null;

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                String strSQL = "SELECT TrackID, Name, Price, AuthorID, Path, Visible FROM Tracks WHERE TrackID = @trackID";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@trackID";
                param.Value = trackID;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        track = new TrackInfo() { Name = sdr["Name"].ToString(), Visible = (bool)sdr["Visible"], Price = sdr["Price"].ToString(), Author = sdr["AuthorID"].ToString(), FilePath = sdr["Path"].ToString() };
                    }
                    return track;
                }
               
            }
            
            return null;
        }

        public bool deleteTrack(string trackId)
        {
            SqlDataReader sdr;

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                String strSQL = "DELETE FROM Tracks WHERE TrackID = @trackId";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@trackId";
                param.Value = trackId;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                return sdr != null ? true : false;              
            }
        }

        public bool changeVisebleMode(string trackId)
        {
            SqlDataReader sdr;
            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                String strSQL = "IF (SELECT Visible FROM Tracks WHERE TrackID = @trackId)=1 BEGIN UPDATE Tracks SET Visible=0 WHERE TrackID = @trackId END ELSE UPDATE Tracks SET Visible=1 WHERE TrackID = @trackId";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@trackId";
                param.Value = trackId;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                return sdr != null ? true : false;
            }
        }

        public bool updateTrack(TrackInfo track)
        {
            SqlDataReader sdr;
            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                String strSQL = "UPDATE Tracks SET Name=@name, Price=@price WHERE TrackID = @trackId";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@trackId";
                param.Value = track.TrackID;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@name";
                param.Value = track.Name;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@price";
                param.Value = track.Price;
                param.SqlDbType = SqlDbType.Float;
                comm.Parameters.Add(param);

                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                return sdr != null ? true : false;
            }
        }

    }
}